﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;

namespace Tayninh2011
{
    public partial class FrmDanhmuc : Form
    {
        private string[,] userInfo = null;

        private string[,] _strTenHuyen = new String[,] { {"TX Tây Ninh",""}, {"Tân Biên",""}, {"Tân Châu",""}, {"Dương Minh Châu",""}, {"Châu Thành",""}, {"Hòa Thành",""}, {"Bến Cầu",""}, {"Gò Dầu",""}, {"Trảng Bàng",""} };
        private string[,] _strTenXaTayNinh = new String[,] { {"Phường 1","1"}, {"Phường 2","7225257"}, {"Phường 3","2"}, {"Phường 4",""}, {"Phường Hiệp Ninh",""}, {"Thạnh Tân",""}, {"Tân Bình",""}, {"Ninh Sơn",""}, {"Bình Minh",""}, {"Ninh Thạnh",""} };
        private string[,] _strTenXaTanBien = new String[,] { {"TT Tân Biên",""}, {"Tân Lập",""}, {"Thạnh Tây",""}, {"Trà Vong",""}, {"Mỏ Công",""}, {"Tân Phong",""}, {"Thạnh Bình",""}, {"Tân Bình",""}, {"Hòa Hiệp",""}, {"Thạnh Bắc",""} };
        private string[,] _strTenXaTanChau = new String[,] { { "TT Tân Châu", "" }, { "Thạnh Đông", "" }, { "Tân Hưng", "" }, { "Tân Hiệp", "" }, { "Tân Hội", "" }, { "Suối Dây", "" }, { "Tân Phú", "" }, { "Tân Đông", "" }, { "Tân Hà", "" }, { "Suối Ngô", "" }, { "Tân Hòa", "" }, { "Tân Thành", "" } };
        private string[,] _strTenXaDuongMinhChau = new String[,] { { "TT Dương Minh Châu", "" }, { "Bàu Năng", "" }, { "Suối Đá", "" }, { "Lộc Ninh", "" }, { "Cầu Khởi", "" }, { "Phan", "" }, { "Chà Là", "" }, { "Truông Mít", "" }, { "Bến Củi", "" }, { "Phước Minh", "" }, { "Phước Ninh", "" } };
        private string[,] _strTenXaChauThanh = new String[,] { { "TT Thanh Điền", "" }, { "Thanh Điền", "" }, { "Hảo Đước", "" }, { "Trí Bình", "" }, { "An Bình", "" }, { "An Cơ", "" }, { "Đồng Khởi", "" }, { "Thái Bình", "" }, { "Long Vĩnh", "" }, { "Biên Giới", "" }, { "Hòa Thạnh", "" }, { "Hòa Hội", "" }, { "Ninh Điền", "" }, { "Phước Vinh", "" }, { "Thành Long", "" } };

        private Hashtable hshHuyen = null;
        private Dictionary<string, string> dicTemplate = new Dictionary<string, string>();


        private object[][] objHuyen;
        private List<string[,]> lstXa=new List<string[,]>();
        private List<List<string[,]>> lstHuyen = new List<List<string[,]>>();

        //so hang hien tai cua bang huyen va xa
        private int intCurrentNumRowHuyen = 0;
        private int intCurrentNumRowXa = 0;
        private string strCurrentValueSelectedCell = "";

        string strTableNameHuyen = "tn_id_huyen";
        List<string[]> lstColumnsHuyen = new List<string[]>();
        List<string[]> lstvaluesHuyen = new List<string[]>();
        string[] fieldsHuyen=new string[]{"stt","tenhuyen","mahuyen"};
        string[][] columnHuyen = new string[][] { new string[] { "stt", "integer" }, new string[] { "tenhuyen", "nvarchar(50)" }, new string[] { "mahuyen", "nvarchar(50) PRIMARY KEY NOT NULL" } };
        
        List<string[]> lstColumnsXa = new List<string[]>();
        List<string[]> lstvaluesXa = new List<string[]>();
        string strTableNameXa = "tn_id_xa";
        string[] fieldsXa = new string[] { "stt", "tenxa", "maxa", "mahuyen" };
        string[][] columnXa = new string[][] { new string[] { "stt", "integer" }, new string[] { "tenxa", "nvarchar(50)" }, new string[] { "maxa", "nvarchar(50) PRIMARY KEY NOT NULL" }, new string[] { "mahuyen", "nvarchar(50)" } };
        //tham so ket noi sqlserver
        private SqlConnection sqlConnection;
        public FrmDanhmuc(string[,] user_info)
        {
            InitializeComponent();
            
            SetUserInfo(user_info);
            //ket noi voi sqlserver
            //bwConnect.RunWorkerAsync();
            ITnToolsTable toolTable = new TnTools();
            this.sqlConnection = toolTable.TnOpenConnection(this.userInfo);

            //objHuyen = new object[][] { new object[] { "TX Tây Ninh","a", "7225" } };
            lstXa.Add(new string[,] {{"TX Tây Ninh","7225"}});
            lstXa.Add(_strTenXaTayNinh);
            lstHuyen.Add(lstXa);
            string strTenHuyen = "";
            string[,] col;
            dgvXa.ColumnCount = 4;
            dgvXa.Columns[0].Name = "Stt";
            dgvXa.Columns[1].Name = "tenxa";
            dgvXa.Columns[2].Name = "maxa";
            dgvXa.Columns[3].Name = "mahuyen";

            dgvHuyen.ColumnCount = 3;
            dgvHuyen.Columns[0].Name = "stt";
            dgvHuyen.Columns[1].Name = "tenhuyen";
            dgvHuyen.Columns[2].Name = "mahuyen";
            //string[] row=new string[3];
            //int num=1;
            //foreach (List<string[,]> l in lstHuyen)
            //{
            //    col = l[0];
            //    row[0] = num.ToString();
            //    row[1] = col[0, 0];
            //    row[2] = col[0, 1];
            //    dgvHuyen.Rows.Add(row);
            //    foreach (string[,] o in l)
            //    {
                    //row[0] = num.ToString();
                    //row[1] = o[0, 0];
                    //row[2] = o[0, 1];
                    //dgvHuyen.Rows.Add(row);
                    //for (int i = 0; i < o.Length / 2; i++)
                    //{
                    //    MessageBox.Show(o.Length.ToString());
                    //    for (int j = 0; j < 2; j++)
                    //    {
                    //        MessageBox.Show(o[i, j].ToString());
                    //    }

                    //}
                    //num++;
            //    }
                
            //}
            
            //tao bang huyen neu chua co
            
            

            
            foreach(string[] c in columnHuyen)
            {
                lstColumnsHuyen.Add(c);
            }
            foreach (string[] c in columnXa)
            {
                lstColumnsXa.Add(c);
            }
            
            lstvaluesHuyen.Add(new string[]{"1","'TX Tây Ninh'","'7225'"});
            lstvaluesXa.Add(new string[] { "1", "'Ph­êng 2'", "'7225257'","'7225'" });
            if (toolTable.TnCreateTable(strTableNameHuyen, userInfo, lstColumnsHuyen))
            {
                //MessageBox.Show("create table ok");
                if (toolTable.TnInsertValue(this.sqlConnection, "tn_id_huyen", fieldsHuyen, lstvaluesHuyen))
                {
                    //MessageBox.Show("insert ok");
                }
                else
                {
                    //MessageBox.Show("insert failed");
                }
            }
            if (toolTable.TnCreateTable(strTableNameXa, userInfo, lstColumnsXa))
            {
                //MessageBox.Show("create table ok");
                if (toolTable.TnInsertValue(this.sqlConnection, "tn_id_xa", fieldsXa, lstvaluesXa))
                {
                    //MessageBox.Show("insert ok");
                }
                else
                {
                    //MessageBox.Show("insert failed");
                }
            }
            else
            {
                //MessageBox.Show("create table failed");
            }
            //bwDanhMucShowData.RunWorkerAsync();
            //load du lieu vao bang huyen
            addHuyen(this.sqlConnection);
            //showDataOnTableXa(this.sqlConnection,"tn_id_xa.mahuyen='7225'");
            intCurrentNumRowHuyen = dgvHuyen.Rows.Count;
            intCurrentNumRowXa = dgvXa.Rows.Count;
        }

        public void SetUserInfo(string[,] info)
        {
            this.userInfo = info;
        }

        private void frmListXa_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'sdeDataSet.TN_DUONG' table. You can move, or remove it, as needed.
            this.tN_DUONGTableAdapter.Fill(this.sdeDataSet.TN_DUONG);

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dgvHuyen_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //dgvXa.Rows.Clear();
            //string[] row = new string[4];
            //string[,] xa;
            //int num = 1;
            //foreach (List<string[,]> l in lstHuyen)
            //{
            //    xa = l[1];
            //    row[0] = num.ToString();
            //    row[1] = xa[0, 0];
            //    row[2] = xa[0, 1];
                
            //    dgvHuyen.Rows.Add(row);
            //}
            if (e.RowIndex < intCurrentNumRowHuyen-1 && e.RowIndex>=0)
            {
                //MessageBox.Show(e.RowIndex.ToString() + "___" + intCurrentNumRowHuyen.ToString());
                string strMaHuyen = dgvHuyen.Rows[e.RowIndex].Cells[2].Value.ToString();
                //MessageBox.Show(strMaHuyen);
                this.dgvXa.Rows.Clear();
                showDataOnTableXa(this.sqlConnection, "tn_id_xa.mahuyen='" + strMaHuyen + "'");
            }
        }

        private void bwDanhMucShowData_DoWork(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            bw.WorkerReportsProgress = true;
            e.Result = showDataBackGround(bw);
        }
        private List<string[]> showDataBackGround(BackgroundWorker bw)
        {
            
            List<string[]> listVal = new List<string[]>();
            if (!bw.CancellationPending)
            {
                ITnToolsTable toolTable = new TnTools();


                listVal = toolTable.TnShowDataUseSQL(this.sqlConnection, "tn_id_huyen", "", 1);
            }
            return listVal;
        }
        private void bwDanhMucShowTable_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            //System.Windows.Forms.Control.ControlCollection controlCol = currentSelectedTabControl.SelectedTab.Controls;
            //DataGridView dtgv = this.dgvHuyen;

            List<string[]> listVal = (List<string[]>)e.Result;
            string[] strRow = listVal[0];

            //this.dgvHuyen.ColumnCount = strRow.Length;
            this.dgvHuyen.Rows.Clear();
            foreach (string[] row in listVal)
            {
                this.dgvHuyen.Rows.Add(row);
            }
        }
        private void addHuyen(SqlConnection sql_connection)
        {
            List<string[]> listVal = new List<string[]>();
            ITnToolsTable toolTable = new TnTools();
            listVal = toolTable.TnShowDataUseSQL(sql_connection, "tn_id_huyen", "", 1);
            //string[] strRow = listVal[0];
            intCurrentNumRowHuyen = listVal.Count;

            //this.dgvHuyen.ColumnCount = strRow.Length;
            this.dgvHuyen.Rows.Clear();
            foreach (string[] row in listVal)
            {
                this.dgvHuyen.Rows.Add(row);
            }
        }
        private void showDataOnTableXa(SqlConnection sql_connection,string dieukien)
        {
            List<string[]> listVal = new List<string[]>();
            ITnToolsTable toolTable = new TnTools();
            listVal = toolTable.TnShowDataUseSQL(sql_connection, "tn_id_xa", dieukien, 1);
            intCurrentNumRowXa = listVal.Count;
            //string[] strRow = listVal[0];

            //this.dgvHuyen.ColumnCount = strRow.Length;
            this.dgvXa.Rows.Clear();
            //MessageBox.Show(listVal.Count.ToString());
            foreach (string[] row in listVal)
            {
                //MessageBox.Show("a");
                this.dgvXa.Rows.Add(row);
            }
        }

        private void bwConnect_DoWork(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            bw.WorkerReportsProgress = true;
            e.Result = connectBackGround(bw);
        }
        private SqlConnection connectBackGround(BackgroundWorker bw)
        {

            SqlConnection sqlConnection = new SqlConnection();
            if (!bw.CancellationPending)
            {
                ITnToolsTable toolTable = new TnTools();


                sqlConnection = toolTable.TnOpenConnection(this.userInfo);
            }
            return sqlConnection;
        }

        private void bwConnect_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.sqlConnection = (SqlConnection)e.Result;
        }

        private void btnLuuHuyen_Click(object sender, EventArgs e)
        {
            //MessageBox.Show( dgvHuyen.Rows.Count.ToString());
            int intNewRowsCount = dgvHuyen.Rows.Count - intCurrentNumRowHuyen;
            List<string[]> values=new List<string[]>();
            string[] value=new string[3];
            for(int i=0;i<intNewRowsCount;i++)
            {
                
                //value=new string[]{dgvHuyen.Rows[intCurrentNumRowHuyen-1+i].Cells[0],dgvHuyen.Rows
                    for(int j=0;j<3;j++)
                    {
                        value[j]="'"+dgvHuyen.Rows[intCurrentNumRowHuyen-1+i].Cells[j].Value.ToString()+"'";
                    }
                values.Add(value);
            }
            
              
            ITnToolsTable toolTable = new TnTools();
            if (toolTable.TnInsertValue(this.sqlConnection, "tn_id_huyen", fieldsHuyen, values))
            {
                //MessageBox.Show("ok");
            }
            else
            {

                //MessageBox.Show("failed");
                //foreach (string[] v in values)
                //{
                //    MessageBox.Show(v[0] + "__" + v[1] + "__" + v[2]);
                //}
                
            }
        }

        private void dgvHuyen_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //MessageBox.Show(dgvHuyen.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());
            //Neu so thu tu hang duoc chon nho hon so hang hien tai cua bang thi update gia tri
            if (e.RowIndex < intCurrentNumRowHuyen-1)
            {
                ITnToolsTable toolTable = new TnTools();
                string valueData ="'"+ dgvHuyen.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString()+"'";
                string fieldData=dgvHuyen.Columns[e.ColumnIndex].Name;
                string fieldKey=dgvHuyen.Columns[2].Name;
                string valueKey="'"+dgvHuyen.Rows[e.RowIndex].Cells[2].Value.ToString()+"'";
                if (e.ColumnIndex == 2)
                    valueKey = strCurrentValueSelectedCell;
                if(toolTable.TnUpdateValue(this.sqlConnection,"tn_id_huyen",fieldData,valueData,fieldKey,valueKey))
                {
                    //MessageBox.Show("update ok");
                    this.lblStatusUpdateHuyen.Visible = true;
                    this.lblStatusUpdateHuyen.Text = "v";
                    this.lblStatusUpdateHuyen.ForeColor = System.Drawing.Color.Green;
                }
                else
                {
                    this.lblStatusUpdateHuyen.Visible = true;
                    this.lblStatusUpdateHuyen.Text = "x";
                    this.lblStatusUpdateHuyen.ForeColor = System.Drawing.Color.Red;
                    //MessageBox.Show("update failed");
                }
            }
        }

        private void dgvHuyen_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dgvHuyen.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
            {
                this.strCurrentValueSelectedCell = dgvHuyen.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
            }
        }

        private void dgvXa_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //MessageBox.Show(e.RowIndex.ToString());

            //string key = dgvXa.Rows[e.RowIndex].Cells[2].Value.ToString();
            

            if (e.RowIndex < intCurrentNumRowXa-1 || intCurrentNumRowXa==1)
            {
                MessageBox.Show(intCurrentNumRowXa.ToString());
                ITnToolsTable toolTable = new TnTools();
                string valueData = "'" + dgvXa.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString() + "'";
                string fieldData = dgvXa.Columns[e.ColumnIndex].Name;
                string fieldKey = dgvXa.Columns[2].Name;
                string valueKey = "'" + dgvXa.Rows[e.RowIndex].Cells[2].Value.ToString() + "'";
                
                if (e.ColumnIndex == 2)
                {
                    valueKey = strCurrentValueSelectedCell;
                }
                
                if (toolTable.TnUpdateValue(this.sqlConnection, "tn_id_xa", fieldData, valueData, fieldKey, valueKey))
                {
                    //MessageBox.Show("update ok");
                    this.lblStatusUpdateXa.Visible = true;
                    this.lblStatusUpdateXa.Text = "v";
                    this.lblStatusUpdateXa.ForeColor = System.Drawing.Color.Green;
                }
                else
                {
                    MessageBox.Show("update failed");
                    this.lblStatusUpdateXa.Visible = true;
                    this.lblStatusUpdateXa.Text = "x";
                    this.lblStatusUpdateXa.ForeColor = System.Drawing.Color.Red;
                    
                }
            }
        }

        private void btnLuuXa_Click(object sender, EventArgs e)
        {
            int intNewRowsCount = dgvXa.Rows.Count - intCurrentNumRowXa;
            List<string[]> values = new List<string[]>();
            string[] value = new string[4];
            for (int i = 0; i < intNewRowsCount; i++)
            {

                //value=new string[]{dgvHuyen.Rows[intCurrentNumRowHuyen-1+i].Cells[0],dgvHuyen.Rows
                for (int j = 0; j < 4; j++)
                {
                    value[j] = "'" + dgvXa.Rows[dgvXa.Rows.Count - 2 + i].Cells[j].Value.ToString() + "'";
                }
                values.Add(value);
            }


            ITnToolsTable toolTable = new TnTools();
            if (toolTable.TnInsertValue(this.sqlConnection, "tn_id_xa", fieldsXa, values))
            {
                //MessageBox.Show("ok");
            }
            else
            {

                //MessageBox.Show("failed");
                //foreach (string[] v in values)
                //{
                //    MessageBox.Show(v[0] + "__" + v[1] + "__" + v[2]);
                //}

            }
        }


    }
}
